import json
import time
from datetime import datetime
from os import times
from urllib.parse import quote_plus

import pandas as pd
import pymysql
import requests
from sqlalchemy import create_engine


def mysql_config():
    user = 'live_data'
    password = quote_plus('CJMg2tsHg7sj6bo0UTKq0a4T!fPDpJ')
    # passwd ='merchantsasd123!@%&'
    host = 'rm-bp17r99475xuauk63yo.mysql.rds.aliyuncs.com'
    # port1 ='3306'
    dbname2 = 'live_data'
    engine2 = create_engine(f"mysql+pymysql://{user}:{password}@{host}:3306/{dbname2}?charset=utf8mb4")
    return engine2


def pymysql_update_notes_id_detail(sql, id_list):

    con = pymysql.connect(host='rm-bp17r99475xuauk63yo.mysql.rds.aliyuncs.com', port=3306, user='live_data',
                          passwd='CJMg2tsHg7sj6bo0UTKq0a4T!fPDpJ', db='live_data', charset='utf8')
    # 获取操作数据的对象 cursor
    cursor = con.cursor()
    cursor.execute(sql, id_list)
    # sql_result = cursor.fetchall()
    # 提交事务
    con.commit()
    # 关闭Cursor
    cursor.close()
    # 关闭链接
    con.close()

    return "修改成功"


def notes_account_huizong(xhs_account):
    """笔记详情计算"""
    import ast
    data = pd.read_sql(
        "select data_desc,xhs_account from live_data.xhs_user where xhs_account='{}'".format(xhs_account),mysql_config())

    data_list = [data.loc[i].to_dict() for i in data.index.values]
    for i in data_list:
        notes_account_data = pd.read_sql("select xhs_account from live_data.xhs_notes_account where xhs_account='{}'".format(i["xhs_account"]),mysql_config())
        print(i["data_desc"])
        try:
            notes_detail_list = ast.literal_eval(i["data_desc"])
            notes_detail_list = [d for d in notes_detail_list if d["id"] != "special.note_time_desc"]
            notes_detail_list = [d for d in notes_detail_list if d["id"] != "special.video"]
            id_list = []
            for j in notes_detail_list:
                id_list.append(j["id"])
            if notes_account_data.empty:

                data_frame = pd.DataFrame([{"xhs_account": i["xhs_account"], "notes_id": id_list}])
                print(data_frame)
                data_frame = data_frame.astype("str")
                pd.io.sql.to_sql(data_frame, 'xhs_notes_account', mysql_config(), schema='live_data',
                                 if_exists='append',
                                 index=False)
            else:
                sql = "update live_data.xhs_notes_account set notes_id = %s where xhs_account='{}'".format(
                    i["xhs_account"])
                print(pymysql_update_notes_id_detail(sql, str(id_list)))
        except:
            pass


    return "计算完成"



def get_xhs_detail(note_ids):
    '''xhs app详情'''
    note_id = note_ids
    token = "xhs_f2807eac95279ad0a4949bf3ede93637_detail"
    url = f"http://47.98.165.184:3007/xhs/detail?token={token}&note_id={note_id}"
    res = requests.get(url)
    return res.text



def push_data_xhs_detail(data):

    data = {
        "bizType": 1,
        "env": 2,
        "secretKey": "Nqfep27LYY3ESEldvALHGnv6Ds56Cs4k",
        "data": data
    }
    # url = "http://apia.intranet.linkinbd.com/tencent/python/pushXhsScrawlDetailData"
    url = "http://127.0.0.1:8085/tencent/python/pushXhsScrawlDetailData"
    # url = self.xhs_config["push.data.address"][""]
    code_status = requests.post(url, json=data)
    print(code_status.text)
    print("详情数据已推送完成")
    return code_status.text

def desc_com(xhs_account):

    import ast
    data = pd.read_sql("select * from live_data.xhs_notes_account where xhs_account='{}'".format(xhs_account),mysql_config())
    notes_desc_data = pd.read_sql("select xhs_account,notes_id from live_data.xhs_notes_desc where xhs_account='{}'".format(xhs_account),mysql_config())
    notes_desc_data_list = notes_desc_data["notes_id"].values.tolist()
    data_list = [data.loc[i].to_dict() for i in data.index.values]
    for i in data_list:
        notes_detail_list = ast.literal_eval(i["notes_id"])
        difference = list(set(notes_detail_list) - set(notes_desc_data_list))
        print("此次新增笔记 ",len(difference),"篇")
        if len(difference) == 0:
            print("*****************不用计算详情数据*****************")
        else:
            for j in difference:
                result = get_xhs_detail(j)
                # print(result)
                data_frame = pd.DataFrame([{"notes_id": j, "notes_desc": result, "xhs_account": i["xhs_account"]}])
                data_frame = data_frame.astype("str")
                print(data_frame)

                pd.io.sql.to_sql(data_frame, 'xhs_notes_desc', mysql_config(), schema='live_data',
                                 if_exists='append',
                                 index=False)

    print("push_data")
    print("在这里推送数据，推送整个账号下边的所有笔记")
    push_notes_detail_data = pd.read_sql("select * from live_data.xhs_notes_desc where xhs_account='{}'".format(xhs_account),mysql_config())
    print("下边数据为",xhs_account,"账号下所有笔记的详情数据")
    print(push_notes_detail_data)
    data_list_desc = [push_notes_detail_data.loc[i].to_dict() for i in push_notes_detail_data.index.values]
    push_detail_data = json.dumps({"xhs_account":xhs_account,"data":data_list_desc},ensure_ascii=False)
    print(push_detail_data)
    push_data_xhs_detail(push_detail_data)
    return "所有笔记详情计算完成，并入库成功!"


def notes_detail_every_day():
    sql_user_info = pd.read_sql("select * from live_data.xhs_user_info",mysql_config())
    print(sql_user_info,"************")
    data_user_info_list = [sql_user_info.loc[i].to_dict() for i in sql_user_info.index.values]

    for i in data_user_info_list:
        # print(i['xhs_account'])
        notes_account_huizong_result = notes_account_huizong(i['xhs_account'])
        print(notes_account_huizong_result)
        print("应该走到这里了")
        if notes_account_huizong_result == "计算完成":
            print(desc_com(i["xhs_account"]))
    import datetime
    print(datetime.datetime.now())


    return "计算完成"


# if __name__ == '__main__':
#     print(notes_detail_every_day())
